import sqlite3, os, csv

# SQL原始数据处理
class Data:
  # 初始化数据 从sql中导入到sqlite
  def __init__(self,dbname="demo1.db",dept="data/dept.csv",emp="data/emp.csv",salgrade="data/salgrade.csv"):
    self.dbname = dbname
    self.con = None
    self.cs = None
    self.dept = dept
    self.emp = emp
    self.salgrade = salgrade
    self.Table1 = """
    create table bonus
    (
      ename varchar(10),
      job varchar(9),
      sal decimal(7,2),
      comm decimal(7,2)
    );
    """
    self.Table2 = """
create table dept
(
deptno integer(4) primary key not null,
dname varchar(14),
loc varchar(13)
);
"""
    self.Table3 = """
create table salgrade
(
grade integer(1),
losal decimal(7,2),
hisal decimal(7,2)
);
"""
    self.SQL = """
create table emp
(
empno integer(4) primary key not null,
ename varchar(10),
job varchar(9),
mgr integer(4),
hiredate date,
sal decimal(7,2),
comm decimal(7,2),
deptno integer(2),
CONSTRAINT fk_deptno
FOREIGN KEY (deptno)
REFERENCES dept(deptno)
);
"""
    self.SQL2 = """
insert into DEPT (DEPTNO, DNAME, LOC) values (10, 'ACCOUNTING', 'NEW YORK');
insert into DEPT (DEPTNO, DNAME, LOC) values (20, 'RESEARCH', 'DALLAS');
insert into DEPT (DEPTNO, DNAME, LOC) values (30, 'SALES', 'CHICAGO');
insert into DEPT (DEPTNO, DNAME, LOC) values (40, 'OPERATIONS', 'BOSTON');

insert into SALGRADE (GRADE, LOSAL, HISAL) values (1, 700, 1200);
insert into SALGRADE (GRADE, LOSAL, HISAL) values (2, 1201, 1400);
insert into SALGRADE (GRADE, LOSAL, HISAL) values (3, 1401, 2000);
insert into SALGRADE (GRADE, LOSAL, HISAL) values (4, 2001, 3000);
insert into SALGRADE (GRADE, LOSAL, HISAL) values (5, 3001, 9999);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE,SAL, COMM, DEPTNO) values (7369, 'SMITH', 'CLERK', 7902, DATE('1980-12-17'),  800.00, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE,SAL, COMM, DEPTNO) values (7499, 'ALLEN', 'SALESMAN', 7698, DATE('1981-02-20'),  1600.00, 300.00, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE,SAL, COMM, DEPTNO) values (7521, 'WARD', 'SALESMAN', 7698, DATE('1981-02-22'),  1250.00, 500.00, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE,SAL, COMM, DEPTNO) values (7566, 'JONES', 'MANAGER', 7839, DATE('1981-04-02'),  2975.00, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE,SAL, COMM, DEPTNO) values (7654, 'MARTIN', 'SALESMAN', 7698, DATE('1982-03-08'),  1250.00, 1400.00, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE,SAL, COMM, DEPTNO) values (7698, 'BLAKE', 'MANAGER', 7839, DATE('1987-08-28'),  2850.00, null, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE,SAL, COMM, DEPTNO) values (7782, 'CLARK', 'MANAGER', 7839, DATE('1981-05-12'), 2450.00, null, 10);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE,SAL, COMM, DEPTNO) values (7788, 'SCOTT', 'ANALYST', 7566, DATE('1982-03-01'),  3000.00, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE,SAL, COMM, DEPTNO) values (7839, 'KING', 'PRESIDENT', null, DATE('1981-06-17'), 5000.00, null, 10);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE,SAL, COMM, DEPTNO) values (7844, 'TURNER', 'SALESMAN', 7698, DATE('1981-09-08'),  1500.00, 0.00, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE,SAL, COMM, DEPTNO) values (7876, 'ADAMS', 'CLERK', 7788, DATE('1981-05-23'),  1100.00, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE,SAL, COMM, DEPTNO) values (7900, 'JAMES', 'CLERK', 7698, DATE('1981-12-03'), 950.00, null, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE,SAL, COMM, DEPTNO) values (7902, 'FORD', 'ANALYST', 7566, DATE('1981-12-03'),  3000.00, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE,SAL, COMM, DEPTNO) values (7934, 'MILLER', 'CLERK', 7782, DATE('1982-01-23'), 1300.00, null, 10);
"""

  # 删除数据库文件
  def removeDb(self):
    if os.path.exists(self.dbname):
      os.remove(self.dbname)

  # 返回sqlite3 数据库文件名
  def dbname(self):
    return self.dbname

  # 打开sqlite3数据库连接和游标
  # 删除之前的sqlite3文件
  def open(self):
    self.removeDb()
    self.con = sqlite3.connect(self.dbname)
    self.cs = self.con.cursor()

  # 只打开sqlite3数据库连接和游标
  def open_only(self):
    self.con = sqlite3.connect(self.dbname)
    self.cs = self.con.cursor()

  # 关闭连接
  def close(self):
    self.cs.close()
    self.con.close()

  # 返回sqlite3连接文件
  def con(self):
    return self.con

  # 返回sqlite3游标文件
  def cs(self):
    return self.cs

  # 通过sql文件进行数据库创建
  def createData(self):
    self.open() 

    # con = sqlite3.connect(self.dbname)
    # cs = con.cursor()

    # python sqlite3为了安全起见 必须一个sql一次commit
    self.cs.execute(self.Table1)
    self.con.commit()
    self.cs.execute(self.Table2)
    self.con.commit()
    self.cs.execute(self.Table3)
    self.con.commit()
    self.cs.execute(self.SQL)
    self.con.commit()
    for x in self.SQL2.split('\n'):
      self.cs.execute(x)
      self.con.commit()
    self.cs.execute("select * from emp")

    rows = self.cs.fetchall()

    for row in rows:
      print(row[0],row[1],row[2],row[3],row[4],row[5],row[6])

    self.con.commit()
    self.close()

  # CSV数据读取
  # 创建数据库表
  # 数据导入数据库
  def csvData(self):
    self.open()
    # 创建表
    self.cs.execute(self.Table1)
    self.con.commit()
    self.cs.execute(self.Table2)
    self.con.commit()
    self.cs.execute(self.Table3)
    self.con.commit()
    self.cs.execute(self.SQL)
    self.con.commit()
    # emp csv数据导入
    with open(self.emp) as f:
      f_csv = csv.reader(f)
      for index,row in enumerate(f_csv):
        if index > 0:
          print('导入emp数据',index,row[1])
          sql = "insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE,SAL, COMM, DEPTNO) values ({empno}, '{ename}', '{job}', {mgr}, DATE('{hiredate}'),  {sal}, {comm}, {deptno});".format(empno=row[0],ename=row[1],job=row[2],mgr=row[3],hiredate=row[4],sal=row[5],comm=row[6],deptno=row[7])
          self.cs.execute(sql)
          self.con.commit()

    # dept csv数据导入
    with open(self.dept) as f:
      f_csv = csv.reader(f)
      for index,row in enumerate(f_csv):
        if index > 0:
          print('导入dept数据',index,row[1])
          sql = "insert into DEPT (DEPTNO, DNAME, LOC) values ({}, '{}', '{}');".format(row[0],row[1],row[2])
          self.cs.execute(sql)
          self.con.commit()

    # salgrade csv数据导入
    with open(self.salgrade) as f:
      f_csv = csv.reader(f)
      for index,row in enumerate(f_csv):
        if index > 0:
          print('导入salgrade数据',index,row[1])
          sql = "insert into SALGRADE (GRADE, LOSAL, HISAL) values ({}, {}, {});".format(row[0],row[1],row[2])
          self.cs.execute(sql)
          self.con.commit()

    self.close()

  # 根据提供的查询sql返回数据
  def getDataBySql(self,sql):
    self.open_only()
    self.cs.execute(sql)
    rows = self.cs.fetchall()

    self.con.commit()
    self.close()
    return rows